﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

using XianShang.Data;
using XianShang.Entity;
using XianShang.Common;

namespace XianShang.Data.SqlServer
{
    public partial class DataProvider : IDataProvider
    {
        
        /// <summary>
        /// 根據用戶ID查找資源模塊
        /// 返回【ACCOUNT】、【MODULENAME】字段
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public DataSet GetModulesByUserId(string userid)
        {
            DbParameter getPar = DbHelper.MakeInParam("@id",(DbType)SqlDbType.Int,int.MaxValue,userid);
            string commandText = "";
            commandText += "SELECT [dbo].[XS_A_User].[Account], [dbo].[XS_A_Module].[Name] AS [ModuleName]";
            commandText += "FROM [dbo].[XS_A_Roles] INNER JOIN [dbo].[XS_A_RolesModule] ON ";
            commandText += "[dbo].[XS_A_Roles].[ID] = [dbo].[XS_A_RolesModule].[RolesID] INNER JOIN";
            commandText += "[dbo].[XS_A_Module] ON [dbo].[XS_A_RolesModule].[ModuleID] = [dbo].[XS_A_Module].[ID] INNER JOIN";
            commandText += "[dbo].[XS_A_UserRoles] ON [dbo].[XS_A_Roles].[ID] = [dbo].[XS_A_UserRoles].[RolesId] INNER JOIN";
            commandText += "[dbo].[XS_A_User] ON [dbo].[XS_A_UserRoles].[UserId] = [dbo].[XS_A_User].[ID]";
            commandText += "WHERE ([dbo].[XS_A_User].[ID] = @id)";
            commandText += "GROUP BY [dbo].[XS_A_User].[Account], [dbo].[XS_A_Module].[Name]";

            return DbHelper.ExecuteDataset(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據模塊ID取得菜單
        /// </summary>
        /// <param name="moduleId"></param>
        /// <returns></returns>
        public DataSet GetMenuByModuleId(string moduleId)
        {
            DbParameter getPar = DbHelper.MakeInParam("@moduleid", (DbType)SqlDbType.Int, int.MaxValue, moduleId);
            string commandText = "SELECT * FROM [dbo].[XS_Menu]WHERE [dbo].[XS_Menu].[ModuleID]=@moduleid";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據PID取得菜單
        /// </summary>
        /// <param name="moduleId"></param>
        /// <returns></returns>
        public DataSet GetMenuByPId(string pid)
        {
            DbParameter getPar = DbHelper.MakeInParam("@pid", (DbType)SqlDbType.Int, int.MaxValue, pid);
            string commandText = "SELECT * FROM [dbo].[XS_Menu]WHERE [dbo].[XS_Menu].[PID]=@pid ORDER BY [MenuOrder] ASC";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據全部模塊信息列表
        /// </summary>
        /// <param name="moduleId"></param>
        /// <returns></returns>
        public DataTable GetAllModuleInfo()
        {
            return DbHelper.ExecuteDataset(CommandType.Text, "SELECT * FROM [dbo].[XS_A_Module]").Tables[0];
        }
        /// <summary>
        /// 根據用戶ID取得菜單信息
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public DataSet GetAllMenuByUserID(string userId)
        {
            DbParameter getPar = DbHelper.MakeInParam("@userid", (DbType)SqlDbType.Int, int.MaxValue, userId);
            string commandText = "SELECT DISTINCT ";
            commandText += "[dbo].[XS_A_Module].[Name] AS [ModuleName], [dbo].[XS_Menu].[MenuName], [dbo].[XS_A_Module].[ModuleOrder],";
            commandText += "[dbo].[XS_Menu].[ModuleID], [dbo].[XS_Menu].[MenuOrder], [dbo].[XS_Menu].[MenuUrl],[dbo].[XS_Menu].[LableName],";
            commandText += "[dbo].[XS_Menu].[LableUrl],[dbo].[XS_Menu].[id] as [menuid],[dbo].[XS_A_Module].[id] as [moduleid]";
            commandText += "FROM ";
            commandText += "[dbo].[XS_A_UserRoles] INNER JOIN ";
            commandText += "[dbo].[XS_A_User] ON [dbo].[XS_A_UserRoles].[UserId] = [dbo].[XS_A_User].[ID] INNER JOIN  ";
            commandText += "[dbo].[XS_A_Roles] ON [dbo].[XS_A_UserRoles].[RolesId] = [dbo].[XS_A_Roles].[ID] INNER JOIN ";
            commandText += "[dbo].[XS_A_RolesModule] ON [dbo].[XS_A_Roles].[ID] = [dbo].[XS_A_RolesModule].[RolesID] INNER JOIN";
            commandText += "[dbo].[XS_A_Module] ON [dbo].[XS_A_RolesModule].[ModuleID] = [dbo].[XS_A_Module].[ID] INNER JOIN ";
            commandText += "[dbo].[XS_Menu] ON [dbo].[XS_A_Module].[ID] = [dbo].[XS_Menu].[ModuleID]";
            commandText += "WHERE ([dbo].[XS_A_User].[ID] = @userid)";
            commandText += "ORDER BY [dbo].[XS_A_Module].[ModuleOrder] ASC";
            return DbHelper.ExecuteDataset(CommandType.Text,commandText,getPar);

        }
        /// <summary>
        /// 根據取得所有菜單信息
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public DataSet GetAllMenu()
        {
            string commandText = "SELECT DISTINCT ";
            commandText += "[dbo].[XS_A_Module].[Name] AS [ModuleName], [dbo].[XS_Menu].[MenuName], [dbo].[XS_A_Module].[ModuleOrder],";
            commandText += "[dbo].[XS_Menu].[ModuleID], [dbo].[XS_Menu].[MenuOrder], [dbo].[XS_Menu].[MenuUrl],[dbo].[XS_Menu].[LableName],";
            commandText += "[dbo].[XS_Menu].[LableUrl],[dbo].[XS_Menu].[id] as [menuid],[dbo].[XS_A_Module].[id] as [moduleid]";
            commandText += "FROM ";
            commandText += "[dbo].[XS_A_Module] INNER JOIN  [dbo].[XS_Menu] ON [dbo].[XS_A_Module].[ID] = [dbo].[XS_Menu].[ModuleID]  ";
            commandText += "ORDER BY [dbo].[XS_A_Module].[ModuleOrder] ASC  ";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText);
        }
        /// <summary>
        /// 根據ID取得菜單信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataSet GetMenuByMenuId(string id)
        {
            DbParameter getPar = DbHelper.MakeInParam("@id", (DbType)SqlDbType.Int, int.MaxValue, id);
            string commandText = "SELECT * FROM [dbo].[XS_Menu] WHERE [dbo].[XS_Menu].[ID]=@id ";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText, getPar);
        }
       
        /// <summary>
        /// 根據ID更新MenuName MenuOrder MenuUrl lablename lableurl三個字段
        /// </summary>
        /// <param name="id"></param>
        /// <param name="name"></param>
        /// <param name="order"></param>
        /// <param name="url"></param>
        /// <returns></returns>
        public int UpDateMenuMenuNameMenuOrderMenuUrlLableNameLableUrlByMenuId(MenuInfo mi)
        {
            DbParameter[] getPar ={
                                  DbHelper.MakeInParam("@id", (DbType)SqlDbType.Int, int.MaxValue, mi.ID),
                                  DbHelper.MakeInParam("@menuname", (DbType)SqlDbType.NVarChar, 50,mi.Name),
                                  DbHelper.MakeInParam("@lablename", (DbType)SqlDbType.NVarChar, 50, mi.LableName),
                                  DbHelper.MakeInParam("@menuorder", (DbType)SqlDbType.TinyInt, 5, mi.Order),
                                  DbHelper.MakeInParam("@menuurl", (DbType)SqlDbType.NVarChar, 200, mi.Url),
                                  DbHelper.MakeInParam("@pid",(DbType)SqlDbType.Int,int.MaxValue,mi.Pid),
                                  DbHelper.MakeInParam("@lableurl", (DbType)SqlDbType.NVarChar, 200, mi.LableUrl)
                                  };
            string commandText = "UPDATE [dbo].[XS_Menu] SET [PID]=@pid,[MenuName]=@menuname,[MenuOrder]=@menuorder,[MenuUrl] =@menuurl,[LableName]=@lablename,[LableUrl]=@lableurl WHERE [id]=@id";
            return DbHelper.ExecuteNonQuery(CommandType.Text,commandText,getPar);
        }
        /// <summary>
        /// 根據ID取得模塊信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataSet GetModuleById(string id)
        {
            DbParameter getPar = DbHelper.MakeInParam("@id", (DbType)SqlDbType.Int, int.MaxValue, id);
            string commandText = "SELECT * FROM [dbo].[XS_A_Module] WHERE [dbo].[XS_A_Module].[ID]=@id ";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據ID更新 Name Order字段
        /// </summary>
        /// <param name="id"></param>
        /// <param name="name"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public int UpDateModuleNameOrderById(string id, string name, string order)
        {
            DbParameter[] getPar ={
                                  DbHelper.MakeInParam("@id", (DbType)SqlDbType.Int, int.MaxValue, id),
                                  DbHelper.MakeInParam("@name", (DbType)SqlDbType.NVarChar, 50, name),
                                  DbHelper.MakeInParam("@order", (DbType)SqlDbType.TinyInt, 5, order)                                 
                                  };
            string commandText = "UPDATE [dbo].[XS_A_Module] SET[Name]=@name,[moduleOrder]=@order WHERE [id]=@id";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 添加菜單
        /// </summary>
        /// <param name="mi"></param>
        /// <returns></returns>
        public int AddMenu(MenuInfo mi)
        {
            DbParameter[] getPar ={
                                  DbHelper.MakeInParam("@menuname", (DbType)SqlDbType.NVarChar, 50,mi.Name),
                                  DbHelper.MakeInParam("@moduleid", (DbType)SqlDbType.Int, int.MaxValue,mi.ModuleId),
                                  DbHelper.MakeInParam("@lablename", (DbType)SqlDbType.NVarChar, 50, mi.LableName),
                                  DbHelper.MakeInParam("@menuorder", (DbType)SqlDbType.TinyInt, 5, mi.Order),
                                  DbHelper.MakeInParam("@menuurl", (DbType)SqlDbType.NVarChar, 200, mi.Url),
                                  DbHelper.MakeInParam("@lableurl", (DbType)SqlDbType.NVarChar, 200, mi.LableUrl),
                                  DbHelper.MakeInParam("@pid",(DbType)SqlDbType.Int,int.MaxValue,mi.Pid),
                                  DbHelper.MakeInParam("@time", (DbType)SqlDbType.DateTime,mi.Time.Length, mi.Time)
                                  };
            string commandText = "INSERT INTO [dbo].[XS_Menu]";
            commandText+="([PID],[ModuleID],[MenuName],[MenuOrder],[MenuUrl],[LableName],[LableUrl],[MenuTimes])";
            commandText += "VALUES(@pid,@moduleid,@menuname,@menuorder,@menuurl,@lablename,@lableurl,@time)";
            return DbHelper.ExecuteNonQuery(CommandType.Text,commandText,getPar);
        }
        /// <summary>
        /// 刪除菜單
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DelMenu(string id)
        {
            DbParameter getPar =DbHelper.MakeInParam("@id", (DbType)SqlDbType.Int, int.MaxValue, id);
            string commandText = "DELETE FROM [dbo].[XS_Menu] WHERE [id]=@id";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
    }
}
